﻿///***************************************************************
/// 시  스  템 : Updms.Mny.Dac
/// 단위시스템 : MNY
/// 계      층 : Data Access Layer
/// 프로그램명 : Mny_Dac_UPDMS_MNY_REPORT_Mny2021r
/// 파  일  명 : Mny_Dac_UPDMS_MNY_REPORT_Mny2021r.cs
/// 설      명 : 기간별출납내역
///***************************************************************
/// 수정ID 일자       이름     내용
///---------------------------------------------------------------
/// CH001  2011-09-01 송시명   최초 작성
///***************************************************************
using System;
using System.Data;
using System.Data.SqlClient;
using System.EnterpriseServices;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Text;

using SMFramework.Data;
using SMFramework.Utility;

namespace Updms.Mny.Dac
{
	[ComVisible(true)]
	[Transaction(TransactionOption.Supported)]
	[JustInTimeActivation(true)]
	public class Mny_Dac_UPDMS_MNY_REPORT_Mny2021r : Base.AppFramework.DacBase
	{
		public Mny_Dac_UPDMS_MNY_REPORT_Mny2021r()
		{
		}

		#region NT [GetRegionalInOutAnalysisList]
		/// <summary>
		/// 기간별 수입/지출 분석표
		/// </summary>
		/// <param name="lo_dp"></param>
		/// <returns></returns>
		[AutoComplete]
		public DataSet GetRegionalInOutAnalysisList(DataPack lo_dp)
		{
			string ls_curr_method
				= MethodBase.GetCurrentMethod().DeclaringType.ToString() + " - " + MethodBase.GetCurrentMethod().ToString();

			StringBuilder sb = new StringBuilder();

			string ls_dt_from = lo_dp["ls_dt_from"].ToString();
			string ls_dt_to = lo_dp["ls_dt_to"].ToString();
			string ls_lang_set = lo_dp["ls_lang_set"].ToString();
			bool lb_is_excel = Convert.ToBoolean(lo_dp["ls_is_excel"].ToString());

			string monthList = DateUtil.GetDiffMonthList(ls_dt_from, ls_dt_to);
			string[] monthLists = monthList.Split(',');

			
			string ls_final_column = "";//최종컬럼
			string ls_total_amt = "";//총액
			string ls_pivot = ""; //피봇

			for (int i = 0; i < monthLists.Length; i++)
			{
				string ym = monthLists[i];

				//최종컬럼
				if (lb_is_excel)
				{
					ls_final_column += "ISNULL([" + ym + "], 0) AS '" + ym + "',\n";
				}
				else
				{
					ls_final_column += "dbo.ufn_UPDMS_Convert_Money(CONVERT(NVARCHAR,ISNULL([" + ym + "], 0))) AS '" + ym + "',\n";
				}

				//총액
				ls_total_amt += "ISNULL([" + ym + "], 0)+";

				//피봇
				ls_pivot += "[" + ym + "]" + ",";	//PIVOT의 IN절
			}

			ls_total_amt = ls_total_amt.Remove(ls_total_amt.Length - 1, 1);

			if (lb_is_excel)
			{
				ls_total_amt = ls_total_amt + " AS Total_Amt";
			}
			else
			{
				ls_total_amt = "dbo.ufn_UPDMS_Convert_Money(" + ls_total_amt + ") AS Total_Amt";
			}
			
			ls_pivot = ls_pivot.Remove(ls_pivot.Length - 1, 1);


			sb.Append("SELECT View_Order,\n");
			sb.Append("       Class,\n");
			sb.Append("       Class_Nm,\n");
			sb.Append("       Account,\n");
			sb.Append("       Account_Nm,\n");
			sb.Append("       " + ls_final_column);
			sb.Append("       " + ls_total_amt);
			sb.Append("  FROM (\n");
			sb.Append("       SELECT t.Class AS View_Order,\n");
			sb.Append("              t.Reg_Dt,\n");
			sb.Append("              t.Class,\n");
			sb.Append("              t.Class_Nm,\n");
			sb.Append("              t.Account,\n");
			sb.Append("              t.Account_Nm,\n");
			sb.Append("              t.Amount\n");
			sb.Append("         FROM (\n");
			sb.Append("              --현금\n");
			sb.Append("              SELECT LEFT(umiom.Reg_Dt, 7) AS Reg_Dt,\n");
			sb.Append("                     umiom.Class,\n");
			sb.Append("                     dbo.ufn_UPDMS_Get_Code_Name(umiom.Class, 'MNY', '" + ls_lang_set + "') AS Class_Nm,\n");
			sb.Append("                     umiom.Account,\n");
			sb.Append("                     dbo.ufn_UPDMS_Get_Code_Name(umiom.Account, 'MNY', '" + ls_lang_set + "') AS Account_Nm,\n");
			sb.Append("                     SUM(ISNULL(umiom.Amount,0)) AS Amount\n");
			sb.Append("                FROM UPDMS_MNY_IN_OUT_MGT umiom WITH(NOLOCK)\n");
			sb.Append("               WHERE LEFT(umiom.Reg_Dt, 7) >= '" + ls_dt_from + "'\n");
			sb.Append("                 AND LEFT(umiom.Reg_Dt, 7) <= '" + ls_dt_to + "'\n");
			sb.Append("               GROUP BY LEFT(umiom.Reg_Dt, 7), umiom.Class, umiom.Account\n");
			sb.Append("              UNION ALL\n");
			sb.Append("              --카드\n");
			sb.Append("              SELECT Reg_Dt,\n");
			sb.Append("                     'AA002' AS Class, --카드는 지출만 있으므로\n");
			sb.Append("                     dbo.ufn_UPDMS_Get_Code_Name('AA002', 'MNY', '" + ls_lang_set + "') AS Class_Nm,\n");
			sb.Append("                     crd.Account AS Account,\n");
			sb.Append("                     MAX(crd.Account_Nm) AS Account_Nm,\n");
			sb.Append("                     SUM(ISNULL(crd.Amount,0)) AS Amount\n");
			sb.Append("                FROM (--일시불\n");
			sb.Append("                     SELECT LEFT(CONVERT(VARCHAR, Paymnt_Dt), 7) AS Reg_Dt,\n");
			sb.Append("                            c.Account,\n");
			sb.Append("                            dbo.ufn_UPDMS_Get_Code_Name(c.Account, 'CRD', '" + ls_lang_set + "') AS Account_Nm,\n");
			sb.Append("                            SUM(ISNULL(c.Amount, 0)) AS Amount\n");
			sb.Append("                       FROM UPDMS_CRD_ITEM_BIZ c WITH(NOLOCK)\n");
			sb.Append("                      WHERE LEFT(c.Paymnt_Dt, 7) >= '" + ls_dt_from + "'\n");
			sb.Append("                        AND LEFT(c.Paymnt_Dt, 7) <= '" + ls_dt_to + "'\n");
			sb.Append("                      GROUP BY LEFT(CONVERT(VARCHAR, c.Paymnt_Dt), 7), c.Account\n");
			sb.Append("                     UNION ALL\n");
			sb.Append("                     --할부\n");
			sb.Append("                     SELECT LEFT(CONVERT(VARCHAR, ucir.Paymnt_Dt), 7) AS Reg_Dt,\n");
			sb.Append("                            ucib.Account,\n");
			sb.Append("                            dbo.ufn_UPDMS_Get_Code_Name(ucib.Account, 'CRD', '" + ls_lang_set + "') AS Account_Nm,\n");
			sb.Append("                            SUM(ISNULL(ucir.Ins_Month_Amt,0)) AS Amount\n");
			sb.Append("                       FROM UPDMS_CRD_INSTALLMENT_REPAY AS ucir WITH(NOLOCK)\n");
			sb.Append("                       JOIN UPDMS_CRD_INSTALLMENT_BIZ AS ucib WITH(NOLOCK) ON ucir.Ins_Cd = ucib.Ins_Cd\n");
			sb.Append("                      WHERE LEFT(ucir.Paymnt_Dt, 7) >= '" + ls_dt_from + "'\n");
			sb.Append("                        AND LEFT(ucir.Paymnt_Dt, 7) <= '" + ls_dt_to + "'\n");
			sb.Append("                      GROUP BY LEFT(CONVERT(VARCHAR, ucir.Paymnt_Dt), 7), ucib.Account\n");
			sb.Append("                     ) crd\n");
			sb.Append("               GROUP BY crd.Reg_Dt, crd.Account\n");
			sb.Append("              ) t\n");
			sb.Append("       UNION ALL\n");
			sb.Append("       --합계\n");
			sb.Append("       SELECT t.Class + 'T' AS View_Order,\n");
			sb.Append("              t.Reg_Dt,\n");
			sb.Append("              t.Class,\n");
			sb.Append("              dbo.ufn_UPDMS_Get_Code_Name(t.Class, 'MNY', '" + ls_lang_set + "') AS Class_Nm,\n");
			sb.Append("              '-' AS Account,\n");
			sb.Append("              dbo.ufn_UPDMS_Get_Code_Name(t.Class, 'MNY', '" + ls_lang_set + "') --수입/지출/저축\n");
			sb.Append("              + ' ' + dbo.ufn_UPDMS_Get_Code_Name('BA002', 'MNY', '" + ls_lang_set + "') AS Account_Nm, --총계\n");
			sb.Append("              SUM(t.Amount) AS Amount\n");
			sb.Append("         FROM (\n");
			sb.Append("              SELECT LEFT(umiom.Reg_Dt, 7) AS Reg_Dt,\n");
			sb.Append("                     umiom.Class,\n");
			sb.Append("                     SUM(ISNULL(umiom.Amount,0)) AS Amount\n");
			sb.Append("                FROM UPDMS_MNY_IN_OUT_MGT umiom WITH(NOLOCK)\n");
			sb.Append("               WHERE LEFT(umiom.Reg_Dt, 7) >= '" + ls_dt_from + "'\n");
			sb.Append("                 AND LEFT(umiom.Reg_Dt, 7) <= '" + ls_dt_to + "'\n");
			sb.Append("               GROUP BY LEFT(umiom.Reg_Dt, 7), umiom.Class\n");
			sb.Append("              UNION ALL\n");
			sb.Append("              --카드\n");
			sb.Append("              SELECT Reg_Dt,\n");
			sb.Append("                     'AA002' AS Class,	--카드는 지출만 있으므로\n");
			sb.Append("                     ISNULL(SUM(CONVERT(MONEY, crd.Amount)), 0) AS Amount\n");
			sb.Append("                FROM (--일시불\n");
			sb.Append("                     SELECT LEFT(CONVERT(VARCHAR, c.Paymnt_Dt), 7) AS Reg_Dt,\n");
			sb.Append("                            SUM(ISNULL(c.Amount, 0)) AS Amount\n");
			sb.Append("                       FROM UPDMS_CRD_ITEM_BIZ c WITH(NOLOCK)\n");
			sb.Append("                      WHERE LEFT(c.Paymnt_Dt, 7) >= '" + ls_dt_from + "'\n");
			sb.Append("                        AND LEFT(c.Paymnt_Dt, 7) <= '" + ls_dt_to + "'\n");
			sb.Append("                      GROUP BY LEFT(CONVERT(VARCHAR, c.Paymnt_Dt), 7)\n");
			sb.Append("                     UNION ALL\n");
			sb.Append("                     --할부\n");
			sb.Append("                     SELECT LEFT(CONVERT(VARCHAR, ucir.Paymnt_Dt), 7) AS Reg_Dt,\n");
			sb.Append("                            SUM(ISNULL(ucir.Ins_Month_Amt,0)) AS Amount\n");
			sb.Append("                       FROM UPDMS_CRD_INSTALLMENT_REPAY AS ucir WITH(NOLOCK)\n");
			sb.Append("                      WHERE LEFT(ucir.Paymnt_Dt, 7) >= '" + ls_dt_from + "'\n");
			sb.Append("                        AND LEFT(ucir.Paymnt_Dt, 7) <= '" + ls_dt_to + "'\n");
			sb.Append("                      GROUP BY LEFT(CONVERT(VARCHAR, ucir.Paymnt_Dt), 7)\n");
			sb.Append("                     ) crd\n");
			sb.Append("               GROUP BY crd.Reg_Dt\n");
			sb.Append("              ) t\n");
			sb.Append("        GROUP BY t.Reg_Dt, t.Class\n");
			sb.Append("       UNION ALL\n");
			sb.Append("       --수입-저축-지출\n");
			sb.Append("       SELECT 'AA004T' AS View_Order,\n");
			sb.Append("              t.Reg_Dt,\n");
			sb.Append("              '-' AS Class,\n");
			sb.Append("              '●' AS Class_Nm,\n");
			sb.Append("              '-' AS Account,\n");
			sb.Append("              dbo.ufn_UPDMS_Get_Code_Name('BA003', 'MNY', '" + ls_lang_set + "') AS Account_Nm,\n");
			sb.Append("              CASE t.Class WHEN 'AA001' THEN sum(t.Amount) * 1	--수입\n");
			sb.Append("                           WHEN 'AA002' THEN sum(t.Amount) * -1	--지출\n");
			sb.Append("                           WHEN 'AA003' THEN sum(t.Amount) * -1	--저축\n");
			sb.Append("              END Amount\n");
			sb.Append("         FROM (\n");
			sb.Append("              --현금\n");
			sb.Append("              SELECT LEFT(umiom.Reg_Dt, 7) AS Reg_Dt,\n");
			sb.Append("                     umiom.Class,\n");
			sb.Append("                     SUM(ISNULL(umiom.Amount,0)) AS Amount\n");
			sb.Append("                FROM UPDMS_MNY_IN_OUT_MGT umiom WITH(NOLOCK)\n");
			sb.Append("               WHERE LEFT(umiom.Reg_Dt, 7) >= '" + ls_dt_from + "'\n");
			sb.Append("                 AND LEFT(umiom.Reg_Dt, 7) <= '" + ls_dt_to + "'\n");
			sb.Append("               GROUP BY LEFT(umiom.Reg_Dt, 7), umiom.Class\n");
			sb.Append("              UNION ALL\n");
			sb.Append("              --카드\n");
			sb.Append("              SELECT Reg_Dt,\n");
			sb.Append("                     'AA002' AS Class, --지출만\n");
			sb.Append("                     ISNULL(SUM(CONVERT(MONEY, crd.Amount)), 0) AS Amount\n");
			sb.Append("                FROM (--일시불\n");
			sb.Append("                     SELECT LEFT(CONVERT(VARCHAR, c.Paymnt_Dt), 7) AS Reg_Dt,\n");
			sb.Append("                            SUM(ISNULL(c.Amount, 0)) AS Amount\n");
			sb.Append("                       FROM UPDMS_CRD_ITEM_BIZ c WITH(NOLOCK)\n");
			sb.Append("                      WHERE LEFT(c.Paymnt_Dt, 7) >= '" + ls_dt_from + "'\n");
			sb.Append("                        AND LEFT(c.Paymnt_Dt, 7) <= '" + ls_dt_to + "'\n");
			sb.Append("                      GROUP BY LEFT(CONVERT(VARCHAR, c.Paymnt_Dt), 7)\n");
			sb.Append("                     UNION ALL\n");
			sb.Append("                     --할부\n");
			sb.Append("                     SELECT LEFT(CONVERT(VARCHAR, ucir.Paymnt_Dt), 7) AS Reg_Dt,\n");
			sb.Append("                            SUM(ISNULL(ucir.Ins_Month_Amt,0)) AS Amount\n");
			sb.Append("                       FROM UPDMS_CRD_INSTALLMENT_REPAY AS ucir WITH(NOLOCK)\n");
			sb.Append("                      WHERE LEFT(ucir.Paymnt_Dt, 7) >= '" + ls_dt_from + "'\n");
			sb.Append("                        AND LEFT(ucir.Paymnt_Dt, 7) <= '" + ls_dt_to + "'\n");
			sb.Append("                      GROUP BY LEFT(CONVERT(VARCHAR, ucir.Paymnt_Dt), 7)\n");
			sb.Append("                     ) crd\n");
			sb.Append("               GROUP BY crd.Reg_Dt\n");
			sb.Append("             ) t\n");
			sb.Append("       GROUP BY t.Reg_Dt, t.Class\n");
			sb.Append("       ) pv\n");
			sb.Append(" PIVOT ( SUM(pv.Amount) FOR pv.Reg_Dt IN (" + ls_pivot + ") ) AS pvt\n");
			sb.Append(" ORDER BY View_Order, dbo.ufn_UPDMS_Get_Code_View_Order(Account, 'MNY')\n");

			//Base.AppFramework.SMEventLog.WriteDataLog("GetRegionalInOutAnalysisList", sb.ToString());

			DataSet ds = new DataSet();

			return _agent.Fill(sb.ToString(), "UPDMS_MNY_REPORT", ds, null, CommandType.Text, ls_curr_method);
		}
		
		#endregion

	}
}
